【TypeORM】パラメータを使いまわすときは型に注意
はじめに
先日、TypeORMでパラメータを使っていたときに少しハマったので、それに関して調べた内容を忘備録として残しておきます。
ハマっていたのは下記のようなSQL文です。
const data = await manager.query('select * from "user" where "updateDt" = $1 and "birth" = $1', ['20230822'])
エラー内容は以下の通りです。
operator does not exist: character varying = date
結論としては、以下のように同じ値の別のパラメータを使って解決できました。
const data = await manager.query('select * from "user" where "updateDt" = $1 and "birth" = $2', ['20230822', '20230822'])
以下、調べた内容です。
事前準備
環境構築
まずは調べるための環境を準備します。
以前、TypeORM CLIを使って環境を簡単に作成する記事を書きましたので、まずはこちらを実施します。
TypeORMで生成されるSQLをCLIを使って確認してみた | DevelopersIO
この記事の「SQLがログに出力されるようにする」までを実施します。
DBマイグレーションを行う
User.tsに以下2行を追加します。updateDtはPostgreSQLのdate型で、birthはvarchar型になります。
@Column({
type: 'date',
})
updateDt: Date
@Column()
birth: string
続いてテーブルを作成するためのマイグレーションファイルを作成します。src/migrationフォルダにファイルが作成されます。
npm run typeorm migration:generate src/migration/CreateUserTable -- --dataSource src/data-source.ts
data-source.tsを以下のように変更します。変更したのは、マイグレーションファイルのクラスをインポートすることと、migrationsにそのクラスを指定することの2点です。
import "reflect-metadata"
import { DataSource } from "typeorm"
import { User } from "./entity/User"
import { CreateUserTable1692676425496 } from "./migration/1692676425496-CreateUserTable"
export const AppDataSource = new DataSource({
type: "postgres",
host: "localhost",
port: 5434,
username: "test",
password: "test",
database: "test",
synchronize: true,
logging: false,
entities: [User],
migrations: [CreateUserTable1692676425496],
subscribers: [],
})
マイグレーションを実行します。
npm run typeorm migration:run -- --dataSource src/data-source.ts
SQLクライアントでDBに接続するとUserテーブルが作成されています。テストデータを1件登録します。
insert into "user" ("firstName" , "lastName" , age, "updateDt" , birth ) values ('Tom', 'Green', 0, '20230822', '20230822' )
これで準備ができたので、パラメータの動きを確認していきます。
パラメータ変数の動きを確認
色々なパターンで検索していきます。
varchar型を文字列で検索
index.tsを以下のように変更します。varchar型であるbirth列をyyyymmddの文字列で検索します。
import { AppDataSource } from "./data-source"
AppDataSource.initialize().then(async (manager) => {
const data = await manager.query('select * from "user" where "birth" = $1', ['20230822'])
console.log(data)
}).catch(error => console.log(error))
当然ですがこの文はエラーにならず、以下のようにデータが取得できます。
[
{
id: 1,
firstName: 'Tom',
lastName: 'Green',
age: 0,
updateDt: 2023-08-21T15:00:00.000Z,
birth: '20230822'
}
]
varchar型を数値で検索
ではvarchar型のbirth列に対してyyyymmddの数値で検索したらどうでしょうか。
const data = await manager.query('select * from "user" where "birth" = $1', [20230822])
こちらもエラーにならずデータが取得できました。
ちなみに、以下のようなSQLをSQLクライアントソフト（DBeaver）で実行してみると、型が異なるため以下のようにエラーになります。
select * from "user" where "birth" = 20230822 -- -> ERROR: operator does not exist: character varying = integer
int型を文字列で検索
続いてint型のage列に対して文字列で検索したらどうでしょうか。
const data = await manager.query('select * from "user" where "age" = $1', ['0'])
これはエラーにならずデータが取得できます。
しかし、'0'ではなく'a'など、数値に変換できない文字列にするとエラーになりました。
invalid input syntax for type integer: "a"
どうやらTypeORMはDBの列と渡されたパラメータの型が違っていたとしても、変換できる値ならば内部的に変換してくれているようですね。数値に対して'a'など変換できない値が渡された場合にエラーになるようです。
date型を文字列で検索
冒頭で述べたエラーになったSQL文では、date型であるupdateDt列をyyyymmddの文字列で検索していました。この条件だけを単独で実行してみます。
const data = await manager.query('select * from "user" where "updateDt" = $1', ['20230822'])
こちらを実行するとエラーにならずデータは取得できました。date型に対して文字列で検索したこと自体はエラーの原因ではないことがわかります。
ちなみに、文字列の形式を'2023.08.22'や'2023/08/22'や'Aug-22-2023'などにしても実行できました。これらはSQLクライアントソフトでも問題なく実行できます。
ただし、'Aug-32-2023'など存在しない日付にするとエラーになります。
date/time field value out of range: "Aug-32-2023"
PostgreSQLで日付として入力可能な形式は以下を参考にしてみてください。
varchar型とdate型を同一の文字列で検索
上記のことから、TypeORMの動きとして以下のようになっていると思われます。
- 最初にパラメータに値を当てはめた時点で、そのパラメータは内部的に適切な型に変換される
- 以降、そのパラメータは変換された型のまま保持される
そのため、今回エラーになった以下の文では、
const data = await manager.query('select * from "user" where "updateDt" = $1 and "birth" = $1', ['20230822'])
- '20230822'という文字列は、date型の
updateDtと比較する際にdate型に変換された - varchar型の
birthとdate型の'20230822'を比較しようとしてエラー
ということが起きていたと考えられます。
ちなみに検索する順番を変えて、"birth" = $1を先にしてみると、エラーの内容が以下のように変わります。
operator does not exist: date = text
この場合は、最初にbirthと比較した際に'20230822'がvarchar型になったため、date型であるupdateDtと比較しようとしてエラーになったことを示しています。
これを解決するためには、パラメータを使いまわさず別々で用意します。
const data = await manager.query('select * from "user" where "updateDt" = $1 and "birth" = $2', ['20230822', '20230822'])
もしくは、型が一致するように変換することでも解決できます。
const data = await manager.query('select * from "user" where "updateDt" = $1 and "birth"::date = $1', ['20230822'])
おわりに
解決してみれば非常にささいなことかもしれませんが、ハマっているときは全然気づけませんでした。この記事ではdate型とvarchar型を取り上げましたが、他の型（int型とvarchar型など）でも同様です。パラメータを使いまわすときは気をつけてみてください。
